using System.Data;

namespace MVP.DataAccess.Test
{
    public class TestDatabaseGateway
    {
        public int GetRowCountFor(string tableName)
        {
            return ExecuteScalar(string.Format("SELECT Count(*) FROM {0}", tableName));
        }

        public int ExecuteScalar(string expression)
        {
            using (IDatabaseConnection connection = new DatabaseConnection())
            {
                IDbCommand command = connection.CreateCommandFor(expression);
                return (int) command.ExecuteScalar();
            }
        }

        public int ExecuteStatement(string sql)
        {
            using (IDatabaseConnection connection = new DatabaseConnection())
            {
                return connection.CreateCommandFor(sql).ExecuteNonQuery();
            }
        }

        public void InsertRowUsing(InsertStatementBuilder builder)
        {
            using (IDatabaseConnection connection = new DatabaseConnection())
            {
                IDbCommand command = connection.CreateCommandFor(builder.ToString());
                foreach (InsertStatementBuilder.Parameter parameter in builder.Parameters)
                {
                    IDbDataParameter commandParameter = command.CreateParameter();
                    commandParameter.ParameterName = parameter.ParameterName;
                    commandParameter.Value = parameter.Value;
                    command.Parameters.Add(commandParameter);
                }

                command.ExecuteNonQuery();
            }
        }

        public int InsertRowAndRetrieveIdUsing(InsertStatementBuilder builder, string idColumnName)
        {
            InsertRowUsing(builder);
            return ExecuteScalar(string.Format("SELECT Max({0}) FROM {1}", idColumnName, builder.TableName));
        }
    }
}